package com.supcon.exchangedata.utils;

import com.supcon.exchangedata.pojo.entity.oracle.Column;
import com.supcon.exchangedata.pojo.entity.oracle.Table;
import freemarker.template.Configuration;
import freemarker.template.Template;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.util.ObjectUtils;

import javax.persistence.EntityManager;
import java.io.*;
import java.util.*;
import java.util.stream.Stream;

/**
 * @author ：icerain.gan
 * @date ：Created in 2020/3/16 16:54
 * @description：数据库导出为word说明文档
 * @modified By：
 * @version: 1.0$
 */
@Component
@Slf4j
public class DataExport {

    /**查询数据库相关信息*/
    private static final String TABLE_SQL =  "SELECT t.table_name tableName,t1.COMMENTS tableComments FROM User_Tab_Cols t, User_Tab_comments t1 where t.table_name = t1.table_name ";
    private static final String COLUMN_SQL =  "SELECT t.colUMN_NAME columnName,t.DATA_TYPE || '(' || T .DATA_LENGTH || ')' dataType,t1.COMMENTS columnComment" +
            " FROM User_Tab_Cols t, User_Col_Comments t1 where t.table_name = t1.table_name and t.column_name = t1.column_name and t.table_name= ";
    private static String ALL_SQL = "SELECT\n" +
            "\tT .table_name tableName,\n" +
            "\tt2.COMMENTS tableComments,\n" +
            "\tT .colUMN_NAME columnName,\n" +
            "\tT .DATA_TYPE || '(' || T .DATA_LENGTH || ')' dateType,\n" +
            "\tt1.COMMENTS columnComment\n" +
            "FROM\n" +
            "\tUser_Tab_Cols T,\n" +
            "\tUser_Col_Comments t1,\n" +
            "\tUser_Tab_comments t2\n" +
            "WHERE\n" +
            "\tT .table_name = t1.table_name\n" +
            "AND T .column_name = t1.column_name\n" +
            "AND T .table_name = t2.table_name\n";
    /**生成word 相关配置文档*/
    @Value(value = "${database.projectName}")
    private String projectName;
    @Value(value = "${database.pathPrefix}")
    private String pathPrefix;
    @Value(value = "${database.templateName}")
    private String templateName;
    @Value(value = "${database.wordFilePath}")
    private String wordFilePath;



    @Qualifier(value = "entityManagerPrimary")
    @Autowired
    private EntityManager entityManager;


    /**
     * 获取数据库数据结构信息
     */
    public void getTableInfos(){
        List tableStream = entityManager.createNativeQuery(ALL_SQL).getResultList();
        String tableNameTemp = "";
        String tableCommentsTemp = "";
        List<Column> columns = new LinkedList<>();
        List<Table> tables = new LinkedList<>();
        for(int i=0;i<tableStream.size();i++){
            Object[] tableObj = (Object[]) tableStream.get(i);
            //初始化判断值
            if(i==0){
                tableNameTemp = valueOf(tableObj[0]);
                tableCommentsTemp = valueOf(tableObj[1]);
            }
            Column column = new Column();
            if(!ObjectUtils.isEmpty(tableObj[2])){
                column.setColumnName(valueOf(tableObj[2]));
            }
            if(!ObjectUtils.isEmpty(tableObj[3])){
                column.setDataType(valueOf(tableObj[3]));
            }
            if(!ObjectUtils.isEmpty(tableObj[4])){
                column.setColumnComment(valueOf(tableObj[4]));
            }
            columns.add(column);
            // 获取table属性值
            String tableName = valueOf(tableObj[0]);
            String tableComments = valueOf(tableObj[1]);
            if(!tableName.equals(tableNameTemp) || tableStream.size()-1 == i){
                Table table = new Table();
                table.setTableName(tableNameTemp);
                table.setTableComments(tableCommentsTemp);
                table.setColumnList(columns);
                tables.add(table);
                columns = new LinkedList<>();
                tableNameTemp = tableName;
                tableCommentsTemp = tableComments;
            }
        }
        createWord(tables);

    }

    /**
     * 该方法会导致oracle 数据库游标超过最大值
     */
    @Deprecated
    public void getTableInfo(){
        Stream tableStream = entityManager.createNativeQuery(TABLE_SQL).getResultStream();
        List<Table> tables = new LinkedList<>();
        tableStream.forEach(e->{
            Table table = new Table();
            Object[] tableValues = (Object[]) e;
            if(tableValues[0] != null){
                table.setTableName(valueOf(tableValues[0]));
                Stream columnStream = entityManager.createNativeQuery(COLUMN_SQL + "'" + table.getTableName() + "'").getResultStream();
                List<Column> columns = new LinkedList<>();
                columnStream.forEach(k->{
                    Column column = new Column();
                    Object[] columnValues = (Object[]) k;
                    if(columnValues[0] != null){
                        column.setColumnName(valueOf(columnValues[0]));
                    }
                    if(columnValues[1] != null){
                        column.setDataType(valueOf(columnValues[1]));
                    }
                    if(columnValues[2] != null){
                        column.setColumnComment(valueOf(columnValues[2]));
                    }
                    columns.add(column);
                });
                table.setColumnList(columns);
            }
            if(tableValues[1] != null){
                table.setTableComments(valueOf(tableValues[0]));
            }
            tables.add(table);
        });
        createWord(tables);

    }

    /**
     * 生成word
     * @param list 数据结构数据
     */
    private void createWord(List<Table> list){
        try {
            Map dataMap = new HashMap();
            dataMap.put("tables", list);
            dataMap.put("projectName",projectName);
            Configuration configuration = new Configuration();
            //设置编码
            configuration.setDefaultEncoding("UTF-8");

            //ftl模板文件
            configuration.setClassForTemplateLoading(DataExport.class, pathPrefix);

            //获取模板
            Template template = configuration.getTemplate(templateName);

            //输出文件
            File outFile = new File(wordFilePath+ File.separator+projectName+"数据库说明文档.doc");

            //如果输出目标文件夹不存在，则创建
            if (!outFile.getParentFile().exists()) {
                outFile.getParentFile().mkdirs();
            }
            //将模板和数据模型合并生成文件
            try (Writer out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFile), "UTF-8"))) {
                //生成文件
                template.process(dataMap, out);
            }
        } catch (Exception e) {
           log.error("数据库设计文档生成失败={}",e.getMessage());
        }


    }

    /**
     * 重写 String.ValueOf()
     * @param obj
     * @return
     */
    private String valueOf(Object obj){
        return (obj == null) ? "" : obj.toString();
    }


}
